按 file 分组统计视图 | 全方位认识 sys 系统库

作者   罗小波·沃趣科技高级数据库技术专家

出品   沃趣科技

在上一篇《按 user 分组统计视图 | 全方位认识 sys 系统库》中，我们介绍了sys 系统库中按 user 分组统计的视图，类似地，本期的内容将为大家介绍按照 file 进行分类统计的视图。下面请跟随我们一起开始 sys 系统库的系统学习之旅吧。

01

io_by_thread_by_latency,x$io_by_thread_by_latency

按照thread ID、processlist ID、用户名分组的 I/O等待时间开销统计信息，默认情况下按照总I/O等待时间降序排序。数据来源：performance_schema.events_waits_summary_by_thread_by_event_name、performance_scgema.threads

下面我们看看使用该视图查询返回的结果。

# 不带x$前缀的视图
root@localhost : sys 12:42:44> select * from io_by_thread_by_latency limit 3;
+-----------------+-------+---------------+-------------+-------------+-------------+-----------+----------------+
| user            | total | total_latency | min_latency | avg_latency | max_latency | thread_id | processlist_id |
+-----------------+-------+---------------+-------------+-------------+-------------+-----------+----------------+
| buf_dump_thread |  880 | 4.67 s        | 2.94 us    | 5.30 ms    | 27.33 ms    |        40 |          NULL |
| main            |  2214 | 3.63 s        | 409.05 ns  | 2.28 ms    | 35.48 ms    |        1 |          NULL |
| root@localhost  |    21 | 88.87 ms      | 527.22 ns  | 2.03 ms    | 21.31 ms    |        49 |              7 |
+-----------------+-------+---------------+-------------+-------------+-------------+-----------+----------------+
3 rows in set (0.01 sec)

# 带x$前缀的视图
root@localhost : sys 12:43:24> select * from x$io_by_thread_by_latency limit 3;
+-----------------+-------+---------------+-------------+-----------------+-------------+-----------+----------------+
| user            | total | total_latency | min_latency | avg_latency    | max_latency | thread_id | processlist_id |
+-----------------+-------+---------------+-------------+-----------------+-------------+-----------+----------------+
| buf_dump_thread |  880 | 4667572388808 |    2938797 | 5304059238.0000 | 27331328412 |        40 |          NULL |
| main            |  2214 | 3626928831147 |      409050 | 2283656763.0000 | 35476899531 |        1 |          NULL |
| root@localhost  |    21 |  88867469637 |      527220 | 2026334846.2500 | 21312776994 |        49 |              7 |
+-----------------+-------+---------------+-------------+-----------------+-------------+-----------+----------------+
3 rows in set (0.01 sec)

视图字段含义如下：

• user：对于前台线程，该列显示与线程关联的account名称（user@host格式），对于后台线程，该列显示后台线程的名称

• total：I/O事件总次数

• total_latency：I/O事件的总延迟时间（执行时间）

• min_latency：I/O事件的单次最小延迟时间（执行时间）

• avg_latency：I/O事件的平均延迟时间（执行时间）

• max_latency：I/O事件的单次最大延迟时间（执行时间）

• thread_id：内部thread ID

• processlist_id：对于前台线程，该列显示为processlist ID，对于后台线程，该列显示为NULL

PS：该视图只统计文件IO等待事件信息("wait/io/file/%")

02

io_global_by_file_by_bytes,x$io_global_by_file_by_bytes

按照文件路径+名称分组的全局I/O读写字节数、读写文件I/O事件数量进行统计，默认情况下按照总I/O(读写字节数)进行降序排序。数据来源：performance_schema.file_summary_by_instance

下面我们看看使用该视图查询返回的结果。

# 不带x$前缀的视图
root@localhost : sys 12:43:27> select * from io_global_by_file_by_bytes limit 3;
+---------------------------------+------------+------------+-----------+-------------+---------------+-----------+-----------+-----------+
| file                            | count_read | total_read | avg_read  | count_write | total_written | avg_write | total    | write_pct |
+---------------------------------+------------+------------+-----------+-------------+---------------+-----------+-----------+-----------+
| @@innodb_data_home_dir/ibtmp1  |          0 | 0 bytes    | 0 bytes  |        2798 | 55.53 MiB    | 20.32 KiB | 55.53 MiB |    100.00 |
| @@innodb_undo_directory/undo002 |        874 | 13.66 MiB  | 16.00 KiB |          0 | 0 bytes      | 0 bytes  | 13.66 MiB |      0.00 |
| @@innodb_data_home_dir/ibdata1  |        31 | 2.50 MiB  | 82.58 KiB |          3 | 64.00 KiB    | 21.33 KiB | 2.56 MiB  |      2.44 |
+---------------------------------+------------+------------+-----------+-------------+---------------+-----------+-----------+-----------+
3 rows in set (0.00 sec)

# 带x$前缀的视图
root@localhost : sys 12:43:44> select * from x$io_global_by_file_by_bytes limit 3;
+-----------------------------------------------+------------+------------+------------+-------------+---------------+------------+----------+-----------+
| file                                          | count_read | total_read | avg_read  | count_write | total_written | avg_write  | total    | write_pct |
+-----------------------------------------------+------------+------------+------------+-------------+---------------+------------+----------+-----------+
| /home/mysql/data/mysqldata1/innodb_ts/ibtmp1  |          0 |          0 |    0.0000 |        2798 |      58228736 | 20810.8420 | 58228736 |    100.00 |
| /home/mysql/data/mysqldata1/undo/undo002      |        874 |  14319616 | 16384.0000 |          0 |            0 |    0.0000 | 14319616 |      0.00 |
| /home/mysql/data/mysqldata1/innodb_ts/ibdata1 |        31 |    2621440 | 84562.5806 |          3 |        65536 | 21845.3333 |  2686976 |      2.44 |
+-----------------------------------------------+------------+------------+------------+-------------+---------------+------------+----------+-----------+
3 rows in set (0.00 sec)

视图字段含义如下：

• file：文件路径+名称

• count_read：读I/O事件总次数

• total_read：读I/O事件的总字节数

• avg_read：读I/O事件的平均字节数

• count_write：写I/O事件总次数

• total_written：写I/O事件的总字节数

• avg_write：写I/O事件的平均字节数

• total：读写I/O事件的总字节数

• write_pct：写I/O事件字节数占文件读写I/O事件的总字节数（读和写总字节数）的百分比

03

io_global_by_file_by_latency,x$io_global_by_file_by_latency

按照文件路径+名称分组的全局I/O事件的时间开销统计信息，默认情况下按照文件总的I/O等待时间(读和写的I/O等待时间)进行降序排序。数据来源：performance_schema.file_summary_by_instance

下面我们看看使用该视图查询返回的结果。

# 不带x$前缀的视图
admin@localhost : sys 09:34:01> admin@localhost : sys 09:34:01> select * from io_global_by_file_by_latency limit 3;
+------------------------------------+-------+---------------+------------+--------------+-------------+---------------+------------+--------------+
| file                              | total | total_latency | count_read | read_latency | count_write | write_latency | count_misc | misc_latency |
+------------------------------------+-------+---------------+------------+--------------+-------------+---------------+------------+--------------+
| @@basedir/share/english/errmsg.sys |    5 | 268.13 ms    |          3 | 119.31 ms    |          0 | 0 ps          |          2 | 148.82 ms    |
| /data/mysqldata1/innodb_ts/ibtmp1  |    51 | 103.21 ms    |          0 | 0 ps        |          47 | 101.96 ms    |          4 | 1.26 ms      |
| /data/mysqldata1/undo/undo003      |  139 | 63.41 ms      |        132 | 60.72 ms    |          1 | 30.11 us      |          6 | 2.65 ms      |
+------------------------------------+-------+---------------+------------+--------------+-------------+---------------+------------+--------------+
3 rows in set (0.01 sec)

# 带x$前缀的视图
admin@localhost : sys 09:34:07> select * from x$io_global_by_file_by_latency limit 3;
+----------------------------------------------+-------+---------------+------------+--------------+-------------+---------------+------------+--------------+
| file                                        | total | total_latency | count_read | read_latency | count_write | write_latency | count_misc | misc_latency |
+----------------------------------------------+-------+---------------+------------+--------------+-------------+---------------+------------+--------------+
| /home/mysql/program/share/english/errmsg.sys |    5 |  268129329000 |          3 | 119307156000 |          0 |            0 |          2 | 148822173000 |
| /data/mysqldata1/innodb_ts/ibtmp1            |    51 |  103214655750 |          0 |            0 |          47 |  101957648625 |          4 |  1257007125 |
| /data/mysqldata1/undo/undo003                |  139 |  63405483000 |        132 |  60724181625 |          1 |      30110625 |          6 |  2651190750 |
+----------------------------------------------+-------+---------------+------------+--------------+-------------+---------------+------------+--------------+
3 rows in set (0.00 sec)

 视图字段含义如下：

• file：文件路径+名称

• total：I/O事件总次数

• total_latency：I/O事件的总延迟时间（执行时间）

• count_read：读I/O事件的总次数

• read_latency：读I/O事件的总延迟时间（执行时间）

• count_write：写I/O事件总次数

• write_latency：写I/O事件的总延迟时间（执行时间）

• count_misc：其他I/O事件总次数

• misc_latency：其他I/O事件的总延迟时间（执行时间）

04

io_global_by_wait_by_bytes,x$io_global_by_wait_by_bytes

按照文件IO事件名称后缀进行分组的统计信息，默认情况下按照总I/O读写总字节数进行降序排序。数据来源：performance_schema.file_summary_by_event_name

下面我们看看使用该视图查询返回的结果。

# 不带x$前缀的视图
admin@localhost : sys 09:35:20> select * from io_global_by_wait_by_bytes limit 1\G
*************************** 1. row ***************************
event_name: innodb/innodb_data_file
      total: 843
total_latency: 439.19 ms
min_latency: 0 ps
avg_latency: 520.99 us
max_latency: 9.52 ms
count_read: 627
total_read: 13.64 MiB
  avg_read: 22.28 KiB
count_write: 60
total_written: 12.88 MiB
avg_written: 219.73 KiB
total_requested: 26.52 MiB
1 row in set (0.01 sec)

# 带x$前缀的视图
admin@localhost : sys 09:35:22> select * from x$io_global_by_wait_by_bytes limit 1\G;
*************************** 1. row ***************************
event_name: innodb/innodb_data_file
      total: 843
total_latency: 439194939750
min_latency: 0
avg_latency: 520990125
max_latency: 9521262750
count_read: 627
total_read: 14303232
  avg_read: 22812.1722
count_write: 60
total_written: 13500416
avg_written: 225006.9333
total_requested: 27803648
1 row in set (0.00 sec)

视图字段含义如下：

• EVENT_NAME：文件IO事件全称去掉了'wait/io/file/'前缀的名称字符串

• total：读写I/O事件发生的总次数

• total_latency：I/O事件的总延迟时间(执行时间)

• min_latency：I/O事件单次最短延迟时间（执行时间）

• avg_latency：I/O事件的平均延迟时间（执行时间）

• max_latency：I/O事件单次最大延迟时间（执行时间）

• count_read：读I/O事件的请求次数

• total_read：读I/O事件的总字节数

• avg_read：读I/O事件的平均字节数

• count_write：写I/O事件的请求次数

• total_written：写I/O事件的总字节数

• avg_written：写I/O事件的平均字节数

• total_requested：读与写I/O事件的总字节数

PS：该视图只统计文件IO等待事件信息("wait/io/file/%")

05

io_global_by_wait_by_latency,x$io_global_by_wait_by_latency

按照事件名称后缀字符串分组、IO延迟时间排序的全局I/O等待时间统计信息，数据来源：performance_schema.file_summary_by_event_name

下面我们看看使用该视图查询返回的结果。

# 不带x$前缀的视图
admin@localhost : sys 09:35:52> select * from io_global_by_wait_by_latency limit 1\G
*************************** 1. row ***************************
event_name: innodb/innodb_data_file
    total: 843
total_latency: 439.19 ms
avg_latency: 520.99 us
max_latency: 9.52 ms
read_latency: 317.18 ms
write_latency: 105.05 ms
misc_latency: 16.96 ms
count_read: 627
total_read: 13.64 MiB
avg_read: 22.28 KiB
count_write: 60
total_written: 12.88 MiB
avg_written: 219.73 KiB
1 row in set (0.01 sec)

# 带x$前缀的视图
admin@localhost : sys 09:35:55> select * from x$io_global_by_wait_by_latency limit 1\G;
*************************** 1. row ***************************
event_name: innodb/innodb_data_file
    total: 843
total_latency: 439194939750
avg_latency: 520990125
max_latency: 9521262750
read_latency: 317177728125
write_latency: 105052561875
misc_latency: 16964649750
count_read: 627
total_read: 14303232
avg_read: 22812.1722
count_write: 60
total_written: 13500416
avg_written: 225006.9333
1 row in set (0.01 sec)

视图字段含义如下：

• EVENT_NAME：文件IO事件全称去掉了'wait/io/file/'前缀的名称字符串

• total：I/O事件的发生总次数

• total_latency：I/O事件的总延迟时间（执行时间）

• avg_latency：I/O事件的平均延迟时间（执行时间）

• max_latency：I/O事件单次最大延迟时间（执行时间）

• read_latency：读I/O事件的总延迟时间（执行时间）

• write_latency：写I/O事件的总延迟时间（执行时间）

• misc_latency：其他混杂I/O事件的总延迟时间（执行时间）

• count_read：读I/O事件的总请求次数

• total_read：读I/O事件的总字节数

• avg_read：读I/O事件的平均字节数

• count_write：写I/O事件的总请求次数

• total_written：写I/O事件的总字节数

• avg_written：写I/O事件的平均字节数

PS：该视图只统计文件IO等待事件信息("wait/io/file/%")

06

latest_file_io,x$latest_file_io

按照文件名称和线程名称分组、文件IO操作开始起始排序的最新的已经执行完成的I/O等待事件信息，数据来源：performance_schema.events_waits_history_long、performance_schema.threads、information_schema.processlist

• 由于等待事件相关的instruments和consumers默认没有开启，所以该视图需要打开相关的配置之后才能查询到数据，语句如下： 

* 打开等待事件的instruments：update setup_instruments set enabled='yes',timed='yes' where name like '%wait/%'; 

* 打开等待事件的consumers：update setup_consumers set enabled='yes' where name like '%wait%';

下面我们看看使用该视图查询返回的结果。

# 不带x$前缀的视图
admin@localhost : sys 09:50:34> select * from latest_file_io limit 3;
+------------------------+-----------------------------------------+----------+-----------+-----------+
| thread                | file                                    | latency  | operation | requested |
+------------------------+-----------------------------------------+----------+-----------+-----------+
| admin@localhost:7      | /data/mysqldata1/slowlog/slow-query.log | 69.24 us | write    | 251 bytes |
| page_cleaner_thread:29 | /data/mysqldata1/innodb_ts/ibtmp1      | 93.30 us | write    | 16.00 KiB |
| page_cleaner_thread:29 | /data/mysqldata1/innodb_ts/ibtmp1      | 16.89 us | write    | 16.00 KiB |
+------------------------+-----------------------------------------+----------+-----------+-----------+
3 rows in set (0.02 sec)

# 带x$前缀的视图
admin@localhost : sys 09:50:36> select * from x$latest_file_io limit 3;
+------------------------+-----------------------------------------+----------+-----------+-----------+
| thread                | file                                    | latency  | operation | requested |
+------------------------+-----------------------------------------+----------+-----------+-----------+
| admin@localhost:7      | /data/mysqldata1/slowlog/slow-query.log | 69240000 | write    |      251 |
| page_cleaner_thread:29 | /data/mysqldata1/innodb_ts/ibtmp1      | 93297000 | write    |    16384 |
| page_cleaner_thread:29 | /data/mysqldata1/innodb_ts/ibtmp1      | 16891125 | write    |    16384 |
+------------------------+-----------------------------------------+----------+-----------+-----------+
3 rows in set (0.01 sec)

视图字段含义如下：

• thread：对于前台线程，显示与线程关联的帐户名和processlist id。对于后台线程，显示后台线程名称和内部thread ID

• file：文件路径+名称

• latency：I/O事件的延迟时间(执行时间)

• operation：I/O操作类型

• requested：I/O事件请求的数据字节数

PS：该视图只统计文件IO等待事件信息("wait/io/file/%")

本期内容就介绍到这里，本期内容参考链接如下：

https://dev.mysql.com/doc/refman/5.7/en/sys-latest-file-io.html

https://dev.mysql.com/doc/refman/5.7/en/sys-io-by-thread-by-latency.html

https://dev.mysql.com/doc/refman/5.7/en/sys-io-global-by-file-by-latency.html

https://dev.mysql.com/doc/refman/5.7/en/sys-io-global-by-wait-by-bytes.html

https://dev.mysql.com/doc/refman/5.7/en/sys-io-global-by-wait-by-latency.html

https://dev.mysql.com/doc/refman/5.7/en/sys-io-global-by-file-by-bytes.html

"翻过这座山，你就可以看到一片海！"。坚持阅读我们的"全方位认识 sys 系统库"系列文章分享，你就可以系统地学完它。 谢谢你的阅读，我们下期不见不散！

| 作者简介

罗小波·沃趣科技高级数据库技术专家

IT从业多年，历任运维工程师，高级运维工程师，运维经理，数据库工程师，曾参与版本发布系统，轻量级监控系统，运维管理平台，数据库管理平台的设计与编写，熟悉MySQL的体系结构时，InnoDB存储引擎，喜好专研开源技术，追求完美。

相关链接

更多干货，欢迎来撩~